package org.ricks.common.db;

import org.ricks.db.TableMeta;
import org.ricks.utils.StrUtil;

import java.util.Iterator;
import java.util.List;

public class SqlBuilder {

    public static String getTableExists(String tableName) {
        return String.format("SHOW TABLES LIKE '%s';", tableName);
    }

    /**
     * SELECT column_name,column_type FROM information_schema.columns WHERE table_name='%s' AND table_schema = (SELECT database());
     * @param tableName
     * @return
     */
    public static String getTableColumn(String tableName) {
        return String.format("SELECT O.column_name columnName,O.column_type columnType,T.INDEX_NAME indexName FROM information_schema.columns O LEFT JOIN INFORMATION_SCHEMA.STATISTICS T ON  (O.table_schema = T.TABLE_SCHEMA AND T.TABLE_NAME = O.TABLE_NAME AND T.COLUMN_NAME = O.COLUMN_NAME) WHERE O.table_schema = (SELECT database()) AND O.TABLE_NAME ='%s'", tableName);
    }

    public static String getCreateTable(TableMeta tableMeta) {
        StringBuilder builder = new StringBuilder();
        builder.append("CREATE TABLE IF NOT EXISTS `").append(tableMeta.getName()).append("` (\n");
        builder.append(" ").append(getCreateColumn(tableMeta.getPrimaryColumn()));
        tableMeta.getColumns().forEach(columnMeta -> {
            if(!columnMeta.isPrimaryKey()) {
                builder.append(",\n ").append(getCreateColumn(columnMeta));
            }
        });
//        builder.append(" PRIMARY KEY (`").append(tableMeta.getPrimaryColumn().getName()).append("`)");
        tableMeta.getIndexKeys().forEach(columnMeta ->
                createIndex(builder,columnMeta)
        );
        builder.append("\n)");
        if (tableMeta.getCharset() != null) {
            builder.append(" DEFAULT CHARACTER SET=").append(tableMeta.getCharset());
            if (tableMeta.getCollate() != null) {
                builder.append(" COLLATE=").append(tableMeta.getCollate());
            }
        }
        if (tableMeta.getComment() != null) {
            builder.append(" COMMENT='").append(tableMeta.getComment()).append("'");
        }
        builder.append(";");
        return builder.toString();
    }

    private static void createIndex(StringBuilder builder,TableMeta.ColumnMeta columnMeta) {
        if(StrUtil.isNotBlank(columnMeta.getIndex())) {
            builder.append(",\n").append(" UNIQUE INDEX `").append(columnMeta.getIndex())
                    .append("` (`").append(columnMeta.getName()).append("`) USING BTREE ");
        } else {
            builder.append(",\n").append(" UNIQUE INDEX `idx_").append(columnMeta.getName())
                    .append("` (`").append(columnMeta.getName()).append("`) USING BTREE ");
        }
    }

    private static String getCreateColumn(TableMeta.ColumnMeta columnMeta) {
        StringBuilder builder = new StringBuilder();
        builder.append(" `").append(columnMeta.getName()).append("` ").append(columnMeta.getType());
        if (columnMeta.isUnsigned()) {
            builder.append(" unsigned");
        }
        if (columnMeta.isNonNull()) {
            builder.append(" NOT NULL");
        } else {
            builder.append(" NULL");
        }
        if (columnMeta.getDefaultValue() != null) {
            builder.append(" DEFAULT '").append(columnMeta.getDefaultValue()).append("' ");
        }
        if(columnMeta.isAuto()) {
            builder.append(" auto_increment ");
        }
        if(columnMeta.isPrimaryKey()) {
            builder.append(" PRIMARY KEY ");
        }
        if (columnMeta.getComment() != null) {
            builder.append(" COMMENT '").append(columnMeta.getComment()).append("'");
        }
        return builder.toString();
    }

    public static String getRemoveColumn(TableMeta tableMeta, TableMeta.ColumnMeta columnMeta) {
        StringBuilder builder = new StringBuilder();
        builder.append("ALTER TABLE ").append(tableMeta.getName()).append(" ");
        builder.append(" DROP COLUMN ").append(columnMeta.getName());
//        builder.append(";");
        return builder.toString();
    }

    public static String getRemoveColumn(TableMeta tableMeta, String columnName) {
        StringBuilder builder = new StringBuilder();
        builder.append("ALTER TABLE ").append(tableMeta.getName()).append(" ");
        builder.append(" DROP COLUMN ").append(columnName);
//        builder.append(";");
        return builder.toString();
    }

    public static String getAddColumn(TableMeta tableMeta, TableMeta.ColumnMeta columnMeta) {
        StringBuilder builder = new StringBuilder();
        builder.append("ALTER TABLE ").append(tableMeta.getName()).append(" ");
        builder.append(" ADD COLUMN").append(getCreateColumn(columnMeta));
//        builder.append(";");
        return builder.toString();
    }

    public static String modifyColumn(TableMeta tableMeta, TableMeta.ColumnMeta columnMeta) {
        StringBuilder builder = new StringBuilder();
        builder.append("ALTER TABLE ").append(tableMeta.getName()).append(" ");
        builder.append(" modify COLUMN").append(getCreateColumn(columnMeta));
//        builder.append(";");
        return builder.toString();
    }

    public static String count(TableMeta tableMeta,String... fileds) {
        if(fileds == null || fileds.length == 0) {
            return count(tableMeta);
        }
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT COUNT( ").append(tableMeta.getPrimaryColumn().getName()).append( ") FROM `").append(tableMeta.getName()).append("` WHERE `");
        for (int i = 0; i < fileds.length; i++) {
            builder.append(fileds[i]).append("` = ? ");
        }
        return builder.toString();
    }

    public static String count(TableMeta tableMeta) {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT COUNT( ").append(tableMeta.getPrimaryColumn().getName()).append( ") FROM ").append(tableMeta.getName());
        return builder.toString();
    }

    public static String getAddColumnIndex(TableMeta tableMeta, TableMeta.ColumnMeta columnMeta) {
        StringBuilder builder = new StringBuilder();
        if(columnMeta.isPrimaryKey()) {
            builder.append("ALTER TABLE `").append(tableMeta.getName()).append("` ");
            builder.append(" ADD ").append(columnMeta.getIndex()).append(" PRIMARY KEY").append("(").append(columnMeta.getName()).append(");");
        } else {
            builder.append("ALTER TABLE `").append(tableMeta.getName()).append("` ");
            builder.append(" ADD ").append(columnMeta.getIndex()).append(" INDEX index_")
                    .append(columnMeta.getName()).append("(").append(columnMeta.getName()).append(")");
        }
        return builder.toString();
    }

    public static String getDelColumnIndex(TableMeta tableMeta, TableMeta.ColumnMeta columnMeta) {
        StringBuilder builder = new StringBuilder();
        builder.append("ALTER TABLE `").append(tableMeta.getName()).append("` ");
        builder.append(" drop ").append(" INDEX index_")
                .append(columnMeta.getName());
        return builder.toString();
    }

    public static String getInsert(TableMeta tableMeta) {
        StringBuilder builder = new StringBuilder();
        builder.append("INSERT INTO  ").append(tableMeta.getName()).append("  (");
        builder.append(" ").append(tableMeta.getPrimaryColumn().getName()).append(" ");
        tableMeta.getColumns().forEach(columnMeta -> {
            if(!columnMeta.isPrimaryKey()) builder.append(", ").append(columnMeta.getName()).append(" ");
        });
        builder.append(") VALUES(?");
        tableMeta.getColumns().forEach(columnMeta -> {
            if(!columnMeta.isPrimaryKey()) builder.append(",?");
        });
        builder.append(") ");
        return builder.toString();
    }

    public static String getDelete(TableMeta tableMeta) {
        StringBuilder builder = new StringBuilder();
        builder.append("DELETE FROM `").append(tableMeta.getName()).append("` WHERE `")
                .append(tableMeta.getPrimaryColumn().getName()).append("` = ? ");
        return builder.toString();
    }

    public static String getUpdate(TableMeta tableMeta) {
        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE ").append(tableMeta.getName()).append(" ");
        if (!tableMeta.getColumns().isEmpty()) {
            builder.append(" SET");
            Iterator<TableMeta.ColumnMeta> iterator = tableMeta.getColumns().stream().filter(columnMeta -> !columnMeta.isPrimaryKey()).iterator();
            if (iterator.hasNext()) {
                while (true) {
                    TableMeta.ColumnMeta columnMeta = iterator.next();
                    if (columnMeta.isReadOnly()) {
                        if (!iterator.hasNext()) {
                            break;
                        }
                    } else{
                        if(columnMeta.isPrimaryKey()) break;
                        builder.append(" ").append(columnMeta.getName()).append(" = ?");
                        if (iterator.hasNext()) {
                            builder.append(",");
                        } else {
                            break;
                        }
                    }
                }
            }
        }
        builder.append(" WHERE ").append(tableMeta.getPrimaryColumn().getName()).append(" = ?");
        return builder.toString();
    }

    public static String getSelect(TableMeta tableMeta) {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT * FROM `").append(tableMeta.getName()).append("` WHERE `")
                .append(tableMeta.getPrimaryColumn().getName()).append("` = ? ");
        return builder.toString();
    }

    public static String getSelect(TableMeta tableMeta,String... fileds) {
        if(fileds.length == 0) {
            return getSelect(tableMeta);
        } else {
            StringBuilder builder = new StringBuilder();
            builder.append("SELECT * FROM `").append(tableMeta.getName()).append("` WHERE `");
            for (int i = 0; i < fileds.length; i++) {
                builder.append(fileds[i]).append("` = ? ");
            }
            return builder.toString();
        }
    }

    public static String getLimitSelect(TableMeta tableMeta,String... fileds) {
        if(fileds.length == 0) {
            return getSelect(tableMeta);
        } else {
            StringBuilder builder = new StringBuilder();
            builder.append("SELECT * FROM `").append(tableMeta.getName()).append("` WHERE `");
            for (int i = 0; i < fileds.length; i++) {
                builder.append(fileds[i]).append("` = ? ");
            }
            builder.append(" limit ?,? ");
            return builder.toString();
        }
    }

    public static String getSelectField(TableMeta tableMeta, List<String> fields) {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT * FROM `").append(tableMeta.getName()).append("`");
        Iterator<String> iterator = fields.iterator();
        if (iterator.hasNext()) {
            builder.append(" WHERE");
            while (true) {
                String key = iterator.next();
                builder.append(" `").append(key).append("` = ?");
                if (iterator.hasNext()) {
                    builder.append(" and");
                } else {
                    break;
                }
            }
        }
//        builder.append(";");
        return builder.toString();
    }

    /**
     * 不能放开查询table 所有数据，线上毁灭性打击
     * @param tableMeta
     * @return
     */
    public static String getSelectAll(TableMeta tableMeta) {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT * FROM `").append(tableMeta.getName()).append("` ");
        return builder.toString();
    }

    public static String getSelectLimit(TableMeta tableMeta) {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT * FROM `").append(tableMeta.getName()).append("` limit ?,? ");
        return builder.toString();
    }

    public static String getMaxPrimaryKey(TableMeta tableMeta) {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT MAX(`").append(tableMeta.getPrimaryColumn().getName()).append("`) FROM `")
                .append(tableMeta.getName()).append("` ");
        return builder.toString();
    }

    public static String getPrimaryColumnSelectField(TableMeta tableMeta, List<String> fields) {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT `").append(tableMeta.getPrimaryColumn().getName()).append("` FROM `")
                .append(tableMeta.getName()).append("`");
        Iterator<String> iterator = fields.iterator();
        if (iterator.hasNext()) {
            builder.append(" WHERE");
            while (true) {
                String key = iterator.next();
                builder.append(" `").append(key).append("` = ?");
                if (iterator.hasNext()) {
                    builder.append(" and");
                } else {
                    break;
                }
            }
        }
//        builder.append(";");
        return builder.toString();
    }

}
